package cn.com.open.itembank.mapper;

import cn.com.open.itembank.dto.*;
import cn.com.open.itembank.entity.*;
import cn.com.open.itembank.sqlprovider.QuestionReadSqlProvider;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;

/**
 * Created by lucky on 2017/9/1.
 */
@Mapper
public interface QuestionReadMapper {

    //region 获取试题信息

    /**
     * 根据试题id获取试题基本信息.
     *
     * @param id 试题id
     * @return 试题基本信息 by id
     */
    @Select("SELECT * FROM Question WHERE QuestionID = #{id}")
    Question getById(@Param("id") String id);

    /**
     * 根据试题id获取试题qti信息.
     *
     * @param id 试题id
     * @return 试题基本信息 content by id
     */
    @Select("SELECT * FROM QuestionContent WHERE QuestionID = #{id}")
    QuestionContent getContentById(@Param("id") String id);
    //endregion

    //region 根据试题id和题库id获取题库试题关系

    /**
     * 根据试题id和题库id获取题库试题关系.
     *
     * @param itemBankId 题库id
     * @param id         试题id
     * @return 题库试题关系 question use relation
     */
    @Select("SELECT * FROM QuestionUseRelation WHERE ItemBankID=#{itemBankId} AND QuestionID = #{id}")
    QuestionUseRelation getQuestionUseRelation(@Param("itemBankId") String itemBankId, @Param("id") String id);
    //endregion

    //region 根据试题id和题库id获取章节试题关系

    /**
     * 根据试题id和题库id获取章节试题关系.
     *
     * @param itemBankId 题库id
     * @param id         试题id
     * @return 题库试题关系 question chapter
     */
    @Select("SELECT TOP 1 * FROM QuestionChapterInfo WHERE ItemBankID=#{itemBankId} AND QuestionID = #{id}")
    QuestionChapterInfo getQuestionChapter(@Param("itemBankId") String itemBankId, @Param("id") String id);
    //endregion

    //region 根据试题id获取试题类别信息

    /**
     * 根据试题id获取试题类别信息.
     *
     * @param id 试题id
     * @return 试题类别列表 question categorys
     */
    @Select("SELECT qc.* FROM QuestionCategory qc INNER JOIN QuestionCategoryRelation qcr ON qc.QuestionCategoryID=qcr.QuestionCategoryID WHERE QuestionID=#{id}")
    List<QuestionCategory> getQuestionCategorys(@Param("id") String id);
    //endregion

    //region 随机获取指定题库指定题型指定数量的试题id集合

    /**
     * 随机获取指定题库指定题型指定数量的试题id集合.
     *
     * @param itemBankId     题库id
     * @param questionTypeId 题型id
     * @param count          获取数量
     * @return 试题id集合 random questions by item bank id and q type
     */
    @Select("SELECT TOP ${count} q.QuestionID FROM Question q " +
            " INNER JOIN QuestionUseRelation qur On qur.QuestionID = q.QuestionID  AND qur.Status = 0" +
            " WHERE qur.ItemBankID = #{itemBankId} " +
            " AND QuestionTypeID=#{questionTypeId} AND q.Status=0 and q.IsCheck=1 " +
            " ORDER BY NEWID()")
    List<String> getRandomQuestionsByItemBankIdAndQType(@Param("itemBankId") String itemBankId, @Param("questionTypeId") String questionTypeId, @Param("count") int count);
    //endregion

    //region 统计相关

    //region 根据题库id获取指定题库下试题按照题型，难度，应用类别的数量统计列表

    /**
     * 根据题库id获取指定题库下试题按照题型，难度，应用类别的数量统计列表.
     *
     * @param itemBankId 题库id
     * @return 数量统计列表 question stat by item bank id
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionStatByItemBankId")
    List<QuestionStateModel> getQuestionStatByItemBankId(@Param("itemBankId") String itemBankId);
    //endregion

    //region 根据题库id获取指定题库下试题按照应用类别的数量统计列表

    /**
     * 根据题库id获取指定题库下试题按照应用类别的数量统计列表.
     *
     * @param itemBankId 题库id
     * @return 数量统计列表 question stat for category by item bank id
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionStatForCategoryByItemBankId")
    List<QuestionCategoryStateModel> getQuestionStatForCategoryByItemBankId(@Param("itemBankId") String itemBankId);
    //endregion

    //region 根据题库id获取指定题库下指定题型的试题按照给定的章节,应用类别，难度范围的数量统计列表

    /**
     * 根据题库id获取指定题库下指定题型的试题按照给定的章节,应用类别，难度范围的数量统计列表.
     *
     * @param itemBankId     题库id
     * @param minDiff        难度系数最大值
     * @param maxDiff        难度系数最小值
     * @param questionTypeId 题型id
     * @param chapterIds     章节id串，逗号分隔，含有单引号
     * @param categoryIds    试题应用类别id传，逗号分隔，含有单引号
     * @return 数量统计列表 question difficulty stat by item bank id
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionDifficultyStatByItemBankId")
    int getQuestionDifficultyStatByItemBankId(@Param("itemBankId") String itemBankId, @Param("minDiff") double minDiff,
                                              @Param("maxDiff") double maxDiff, @Param("questionTypeId") String questionTypeId,
                                              @Param("chapterIds") String chapterIds, @Param("categoryIds") String categoryIds);
    //endregion

    //region 根据试题数量查询模型获取指定条件下试题数量（策略用）

    /**
     * 根据试题数量查询模型获取指定条件下试题数量（策略用）.
     *
     * @param filterModel the filter model
     * @param getCount    the get count
     * @return the question count for rules check
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionCountForRulesCheck")
    int getQuestionCountForRulesCheck(@Param("filterModel") QuestionCountFilterModel filterModel, @Param("getCount") boolean getCount);
    //endregion

    //region 根据试题数量查询模型获取指定条件下试题（策略组卷用）

    /**
     * 根据试题数量查询模型获取指定条件下试题（策略组卷用）.
     *
     * @param filterModel the filter model
     * @param getCount    the get count
     * @return the question count for rules check
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionCountForRulesCheck")
    List<RuleQuestionModel> getQuestionForRules(@Param("filterModel") QuestionCountFilterModel filterModel, @Param("getCount") boolean getCount);
    //endregion

    //region 根据题库id，题型id，所属类别id查询试题id列表（策略组卷用）

    /**
     * 根据题库id，题型id，所属类别id查询试题id列表.
     *
     * @param itemBankID         the item bank id
     * @param questionTypeID     the question type id
     * @param questionCategoryID the question category id
     * @return the questions by categories for rules
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionsByCategoriesForRules")
    List<String> getQuestionsByCategoriesForRules(@Param("itemBankID") String itemBankID, @Param("questionTypeID") String questionTypeID,
                                                  @Param("questionCategoryID") String questionCategoryID);
    //endregion

    //region 根据试题id获取曝光表和策略试题关系表中使用数量总计

    /**
     * 根据试题id获取曝光表和策略试题关系表中使用数量总计.
     *
     * @param id      试题id
     * @param paperId 试卷id
     * @return 数量统计列表 question relation count
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getQuestionRelationCount")
    int getQuestionRelationCount(@Param("id") String id, @Param("paperId") String paperId);
    //endregion

    //endregion

    //region 获取试题列表

    /**
     * 获取试题列表
     * 试题信息 examination question information
     *
     * @param type           the type
     * @param filterModelDto the filter model dto
     * @param sql            the question list sql
     * @return the question list
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getListQuery")
    List<QueryFilterModelDto> getListQuery(@Param("type") String type, @Param("filterModelDto") FilterModelDto filterModelDto, @Param("sql") String sql);

    /**
     * 获取列表个数
     * 试题信息 examination question information
     *
     * @param type the type
     * @param sql  the sql
     * @return the recyle bin count
     */
    @SelectProvider(type = QuestionReadSqlProvider.class, method = "getListCount")
    int getListCount(@Param("type") String type, @Param("sql") String sql);

    //endregion

    //region 根据i2~i7获取重复试题的试题id
    /**
     * 根据i2~i7获取重复试题的试题id.
     *
     * @param itemBankId the item bank id
     * @param json        the xml
     * @return the same question
     */
    @Select("SELECT newQc.QuestionID " +
            "FROM (" +
            "SELECT qc.QuestionID, " +
            "(CASE " +
            "WHEN LEN(convert(varchar(max), qc.ContentJson))=0 " +
            "THEN '' " +
            "WHEN CHARINDEX('\"I8\":', qc.ContentJson) - CHARINDEX('\"I2\":', qc.ContentJson)<=0 " +
            "THEN '' " +
            "ELSE " +
            "SUBSTRING(convert(varchar(max), qc.ContentJson), CHARINDEX('\"I2\":', qc.ContentJson), CHARINDEX('\"I8\":', qc.ContentJson) - CHARINDEX('\"I2\":', qc.ContentJson) - 1) " +
            "END) " +
            "AS json " +
            "FROM QuestionContent qc " +
            ") as newQc " +
            "INNER JOIN QuestionUseRelation re ON re.QuestionID  = newQc.QuestionID AND re.Status IN (0,1) " +
            "INNER JOIN Question q ON q.QuestionID = newQc.QuestionID AND q.Status IN (0,1) " +
            "WHERE re.ItemBankID = #{itemBankId} AND newQc.json = #{json}")
    List<String> getSameQuestion(@Param("itemBankId") String itemBankId, @Param("json") String json);
    //endregion

    //region 根据题库id获取章节试题关系
    /**
     * 根据题库id获取章节试题关系.
     *
     * @param itemBankId 题库id
     * @return 题库试题关系 question chapter
     */
    @Select("SELECT * FROM QuestionChapterInfo WHERE ItemBankID=#{itemBankId}")
    List<QuestionChapterInfo> getQuestionChapterByItemBank(@Param("itemBankId") String itemBankId);

    /**
     * 根据类别名称获取类别id
     * @param questionCategoryName 类别名称
     * @return
     */
    @Select("SELECT QuestionCategoryID FROM QuestionCategory WHERE QuestionCategoryName = #{questionCategoryName}")
    String getByQuestionCategoryId(@Param("questionCategoryName") String questionCategoryName);

    /**
     * 根据试题ID获取试题曝光表信息
     * @param id
     * @return
     */
    @Select("SELECT * FROM QuestionDisplayInfo WHERE QuestionID=#{QuestionID}")
    QuestionDisplayInfo getQuestionDisplayInfo(@Param("QuestionID") String id);
/**********************************************************************************************/



    /**
     *
     * @param itemBankId
     * @return
     */
    @Select("SELECT * FROM CourseItemBank WHERE ItemBankID = #{id} AND Status = 0")
    CourseItemBank getEnabledById(String itemBankId);
    //endregion
}
